import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

material_use_router = APIRouter(prefix="/material/useHead", tags=["物料管理"], dependencies=[Depends(oauth2_scheme)])


class MaterialReservationSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="预约开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="预约结束日期(yyyyMMdd)")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fbillno: Optional[str] = Field("", description="单据编号(支持前缀匹配)")
    fbillstate: Optional[str] = Field("1", description="单据状态编码，默认1")
    fbilltype: Optional[str] = Field("1", description="单据类型编码，默认1")
    fuseuser: Optional[str] = Field("", description="使用人ID")


@material_use_router.get("/list", summary="物料预约查询接口")
def list_material_use(search: MaterialReservationSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_use_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fuseuser != "":
        sql = sql + " and fuseuser = :fuseuser "
        param["fuseuser"] = search.fuseuser
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fusedate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fuseuser,fusedate,fplanusedate,"
    sql = sql + "fpoint_no,fmancount,fplanbegtime,fplanendtime,"
    sql = sql + "frealbegtime,frealendtime,fexplanation,faudituser,"
    sql = sql + "fauditdate,fauditnote,fupddate,foperator,"
    sql = sql + " fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from i_tc_use_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fuseuser != "":
        sql = sql + " and fuseuser = :fuseuser "
        param["fuseuser"] = search.fuseuser
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fusedate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql = sql + " order by fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.frfidbarcode,b.fgoodno,b.fbuyprice,"
    sql_3 = sql_3 + " b.fbalqty,b.fretqty,b.fuseqty,b.fboxbarcode,b.fcabbarcode,"
    sql_3 = sql_3 + " b.foperator,b.fupddate,b.fmemo,b.fprocdate,c.fgoodname"
    sql_3 = sql_3 + " from i_tc_use_head a,i_tc_use_detail b,i_ts_good_info c  "
    sql_3 = sql_3 + " where a.fbillno=b.fbillno"
    sql_3 = sql_3 + " and b.fgoodno=c.fgoodno"
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fuseuser != "":
        sql_3 = sql_3 + " and fuseuser = :fuseuser "
        param["fuseuser"] = search.fuseuser
    if search.fpointNo != "":
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbilltype != "":
        sql_3 = sql_3 + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(fusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fusedate = ""
        try:
            fusedate = data[key][3].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fusedate == ""):
            fusedate = None

        fplanusedate = ""
        try:
            fplanusedate = data[key][4].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fplanusedate == ""):
            fplanusedate = None

        fupddate = ""
        try:
            fupddate = data[key][15].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        fcheckdate = ""
        try:
            fcheckdate = data[key][17].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fcheckdate == ""):
            fcheckdate = None

        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fuseuser'] = data[key][2]
        d1['fusedate'] = fusedate
        d1['fplanusedate'] = fplanusedate
        d1['fpointNo'] = data[key][5]
        d1['fmancount'] = data[key][6]
        d1['fplanbegtime'] = data[key][7]
        d1['fplanendtime'] = data[key][8]
        d1['frealbegtime'] = data[key][9]
        d1['frealendtime'] = data[key][10]
        d1['fexplanation'] = data[key][11]
        d1['faudituser'] = data[key][12]
        d1['fauditdate'] = data[key][13]
        d1['fauditnote'] = data[key][14]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][16]
        d1['fcheckdate'] = fcheckdate
        d1['fcheckuser'] = data[key][18]
        d1['fbillstate'] = data[key][19]
        d1['fbilltype'] = data[key][20]
        d1['ftakedateEnd'] = None
        d1['ftakedateStart'] = None
        d1['fupddatestr'] = None
        d1['fupddatestr2'] = None
        d1['stringAgg'] = None
        d1['fpointName'] = None
        d1['fbillstateStr'] = None
        d1['username'] = None
        d1['towername'] = None
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['frfidbarcode'] = data_detail[j1][2]
                d2['fgoodno'] = data_detail[j1][3]
                d2['fbuyprice'] = data_detail[j1][4]
                d2['fbalqty'] = data_detail[j1][5]
                d2['fretqty'] = data_detail[j1][6]
                d2['fuseqty'] = data_detail[j1][7]
                d2['fboxbarcode'] = data_detail[j1][8]
                d2['fcabbarcode'] = data_detail[j1][9]
                d2['foperator'] = data_detail[j1][10]
                d2['fupddate'] = data_detail[j1][11]
                d2['fmemo'] = data_detail[j1][12]
                d2['fprocdate'] = data_detail[j1][13]
                d2['fbillstate'] = None
                d2['fbillstatestr'] = None
                d2['fboxname'] = None
                d2['fcabname'] = None
                d2['fequipmenttag'] = None
                d2['fgoodname'] = data_detail[j1][14]
                d2['fpointNo'] = None
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class MaterialReservationDetailCreate(BaseModel):
    """物料预约明细创建参数"""
    fline: int = Field(..., description="明细行号")
    fgoodno: str = Field(..., description="物料编号")
    frfidbarcode: str = Field(..., description="RFID条码")
    fbuyprice: float = Field(0, description="单价，默认0")
    fbalqty: float = Field(0, description="库存数量，默认0")
    fretqty: float = Field(0, description="退回数量，默认0")
    fuseqty: float = Field(0, description="使用数量，默认0")
    fboxbarcode: str = Field("", description="箱子条码")
    fcabbarcode: str = Field("", description="柜子条码")
    fmemo: str = Field("", description="备注")
    fprocdate: Optional[str] = Field(None, description="处理日期(yyyy-MM-dd)")
    fgoodname: str = Field(..., description="物料名称")


class MaterialReservationCreate(BaseModel):
    """物料预约创建请求参数"""
    fbillno: str = Field(..., description="预约单号")
    fusedate: str = Field(..., description="使用日期(yyyy-MM-dd)")
    fuseuser: str = Field(..., description="使用人ID")
    fsiteNo: str = Field(..., description="站点编号")
    fplanusedate: str = Field(..., description="计划使用日期(yyyy-MM-dd)")
    fpointNo: str = Field("", description="点位编号")
    fmancount: int = Field(1, description="使用人数，默认1")
    fplanbegtime: Optional[str] = Field(None, description="计划开始时间(HH:MM:SS)")
    fplanendtime: Optional[str] = Field(None, description="计划结束时间(HH:MM:SS)")
    frealbegtime: Optional[str] = Field(None, description="实际开始时间(HH:MM:SS)")
    frealendtime: Optional[str] = Field(None, description="实际结束时间(HH:MM:SS)")
    fexplanation: str = Field("", description="使用说明")
    fbilltype: str = Field("1", description="单据类型，默认1")
    details: List[MaterialReservationDetailCreate] = Field(..., description="物料明细列表")


@material_use_router.post("/create", summary="物料预约新增接口")
def create_material_use(create: MaterialReservationCreate, db: Session = Depends(get_db)):
    try:
        fbillno = create.fbillno
        d1 = {}
        detail_list = []
        with db.begin():
            details = create.details
            for detail in details:
                sql = "insert into i_tc_use_detail(fbillno,fline,fgoodno,fbuyprice,fbalqty,fretqty,fuseqty,fprocdate,frfidbarcode,fboxbarcode,fcabbarcode,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fgoodno , :fbuyprice , :fbalqty , :fretqty , :fuseqty , :fprocdate , :frfidbarcode , :fboxbarcode , :fcabbarcode , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": detail.fline,
                    "fgoodno": detail.fgoodno,
                    "fbuyprice": detail.fbuyprice,
                    "fbalqty": detail.fbalqty,
                    "fretqty": detail.fretqty,
                    "fuseqty": detail.fuseqty,
                    "fprocdate": detail.fprocdate,
                    "frfidbarcode": detail.frfidbarcode,
                    "fboxbarcode": detail.fboxbarcode,
                    "fcabbarcode": detail.fcabbarcode,
                    "fmemo": detail.fmemo,
                })

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = detail.fline
                d2['fgoodno'] = detail.fgoodno
                d2['fgoodname'] = detail.fgoodname
                d2['fbuyprice'] = detail.fbuyprice
                d2['fbalqty'] = detail.fbalqty
                d2['fretqty'] = detail.fretqty
                d2['fuseqty'] = detail.fuseqty
                d2['fmemo'] = detail.fmemo
                d2['frfidbarcode'] = detail.frfidbarcode
                d2['fprocdate'] = detail.fprocdate
                d2['fboxbarcode'] = detail.fboxbarcode
                d2['fcabbarcode'] = detail.fcabbarcode
                detail_list.append(d2)
            sql = "insert into i_tc_use_head(fbillno,fsite_no,fuseuser,fusedate"
            sql = sql + ",fplanusedate,fpoint_no,fmancount,fplanbegtime"
            sql = sql + ",fplanendtime,frealbegtime,frealendtime"
            sql = sql + ",fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :fuseuser , :fusedate "
            sql = sql + ", :fplanusedate , :fpointNo , :fmancount , :fplanbegtime "
            sql = sql + ", :fplanendtime , :frealbegtime , :frealendtime "
            sql = sql + ", :fexplanation ,now(), :fuseuser ,1, :fbilltype )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": create.fsiteNo,
                "fuseuser": create.fuseuser,
                "fusedate": create.fusedate,
                "fplanusedate": create.fplanusedate,
                "fpointNo": create.fpointNo,
                "fmancount": create.fmancount,
                "fplanbegtime": create.fplanbegtime,
                "fplanendtime": create.fplanendtime,
                "frealbegtime": create.frealbegtime,
                "frealendtime": create.frealendtime,
                "fexplanation": create.fexplanation,
                "fbilltype": create.fbilltype,
            })
            d1['fbillno'] = fbillno
            d1['fsiteno'] = create.fsiteNo
            d1['fuseuser'] = create.fuseuser
            d1['fusedate'] = create.fusedate
            d1['fplanusedate'] = create.fplanusedate
            d1['fpointNo'] = create.fpointNo
            d1['fmancount'] = create.fmancount
            d1['fplanbegtime'] = create.fplanbegtime
            d1['fplanendtime'] = create.fplanendtime
            d1['frealbegtime'] = create.frealbegtime
            d1['frealendtime'] = create.frealendtime
            d1['fexplanation'] = create.fexplanation
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fupddate'] = None
            d1['foperator'] = create.fuseuser
            d1['fbillstate'] = 1
            d1['fbilltype'] = create.fbilltype
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@material_use_router.post("/update", summary="物料预约修改接口")
def update_material_use(update: MaterialReservationCreate, db: Session = Depends(get_db),
                        token: str = Depends(oauth2_scheme)):
    try:
        fbillno = update.fbillno
        fsiteNo = update.fsiteNo
        with db.begin():
            sql = "delete from i_tc_use_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            details = update.details
            for detail in details:
                sql = "insert into i_tc_use_detail(fbillno,fline,fgoodno,fbuyprice,fbalqty,fretqty,fuseqty,fprocdate,frfidbarcode,fboxbarcode,fcabbarcode,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fgoodno , :fbuyprice , :fbalqty , :fretqty , :fuseqty , :fprocdate , :frfidbarcode , :fboxbarcode , :fcabbarcode , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": detail.fline,
                    "fgoodno": detail.fgoodno,
                    "fbuyprice": detail.fbuyprice,
                    "fbalqty": detail.fbalqty,
                    "fretqty": detail.fretqty,
                    "fuseqty": detail.fuseqty,
                    "fprocdate": detail.fprocdate,
                    "frfidbarcode": detail.frfidbarcode,
                    "fboxbarcode": detail.fboxbarcode,
                    "fcabbarcode": detail.fcabbarcode,
                    "fmemo": detail.fmemo,
                })
            sql = "update i_tc_use_head "
            sql = sql + " set fuseuser= :fuseuser ,fusedate= :fusedate "
            sql = sql + ",fplanusedate= :fplanusedate ,fpoint_no= :fpointNo "
            sql = sql + ",fmancount= :fmancount ,fplanbegtime= :fplanbegtime "
            sql = sql + ",fplanendtime= :fplanendtime ,frealbegtime= :frealbegtime ,frealendtime= :frealendtime "
            sql = sql + ",fexplanation= :fexplanation ,fupddate=now(),foperator= :fuseuser "
            sql = sql + " where fbillno= :fbillno and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fuseuser": update.fuseuser,
                "fusedate": update.fusedate,
                "fplanusedate": update.fplanusedate,
                "fpointNo": update.fpointNo,
                "fmancount": update.fmancount,
                "fplanbegtime": update.fplanbegtime,
                "fplanendtime": update.fplanendtime,
                "frealbegtime": update.frealbegtime,
                "frealendtime": update.frealendtime,
                "fexplanation": update.fexplanation,
                "fbillno": update.fbillno,
                "fsiteNo": update.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@material_use_router.post("/delete/{fbillno}", summary="物料预约删除接口")
def delete_material_use(fbillno: str = Path(description="单号"), db: Session = Depends(get_db)):
    try:
        param = {"fbillno": fbillno}
        with db.begin():
            sql = "select count(1) from i_tc_use_head where fbillno= :fbillno  and fbillstate>1"
            data = db.execute(text(sql), param).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该订单不能删除")
            else:
                sql = "delete from i_tc_use_detail where fbillno= :fbillno "
                db.execute(text(sql), param)
                sql = "delete from i_tc_use_head where fbillno= :fbillno "
                db.execute(text(sql), param)
                return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
